package com.teradata.tool.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.teradata.tool.bean.ColumnDefInfoBean;
import com.teradata.tool.db.DbUtil;
import com.teradata.tool.util.StringUtils;

public class ColumnDefDao {
	private static final String	QUERY_COLUMN_DEF_INFOS	= "select distinct cr.rundate,shi.developer,cr. sourceTab,shi.table_comment,cr.sourceCol,cr.checktype,cr.allrowcnt,cr.faildRowCnt,cr.qerysql from "
																+ DbUtil.getDbConfigurationInfo().getDbDqc()
																+ ".t01_CHECK_RESULT cr,"
																+ DbUtil.getDbConfigurationInfo().getDbWork()
																+ ".sdm_head_info shi "
																+ "where cr.FaildRowCnt > 0 and cr.rundate>=? and cr.rundate<=? and cr.checktype not in ('01','03','08','09','10')and cr.sourceTab=shi.target_table  order by cr.rundate,shi.developer;";

	private static final String	QUERY_PRE_MAX_RUNDATE	= "SELECT MAX(RUNDATE) FROM "
																+ DbUtil.getDbConfigurationInfo().getDbDqc()
																+ ".T01_CHECK_RESULT WHERE CHECKTYPE NOT IN ('01','03','08','09','10') AND RUNDATE < ?";

	public static List<ColumnDefInfoBean> queryColumnDefInfoBeans(Connection conn, Date txdate_start, Date txdate_end)
			throws SQLException {
		List<ColumnDefInfoBean> ret = new ArrayList<ColumnDefInfoBean>();
		PreparedStatement stat = conn.prepareStatement(QUERY_COLUMN_DEF_INFOS);
		try {
			stat.setDate(1, txdate_start);
			stat.setDate(2, txdate_end);
			ResultSet rs = stat.executeQuery();
			try {
				while (rs.next()) {
					ColumnDefInfoBean info = new ColumnDefInfoBean();
					int i = 1;
					info.setTxDate(rs.getDate(i++));
					info.setDeveloper(rs.getString(i++));
					info.setSourceTable(rs.getString(i++));
					info.setTableComment(rs.getString(i++));
					info.setSourceCol(rs.getString(i++));
					info.setCheckType(rs.getString(i++));
					info.setAllRowCnt(rs.getInt(i++));
					info.setFaildRowCnt(rs.getInt(i++));
					info.setQuerySql(rs.getString(i++));
					ret.add(info);
				}
			} finally {
				rs.close();
			}
		} finally {
			stat.close();
		}
		return ret;
	}

	public static List<ColumnDefInfoBean> queryPreColumnDefInfoBeans(Connection conn, Date txdate_start)
			throws Exception {
		List<ColumnDefInfoBean> ret = new ArrayList<ColumnDefInfoBean>();
		PreparedStatement stat = conn.prepareStatement(QUERY_PRE_MAX_RUNDATE);
		try {
			stat.setDate(1, txdate_start);
			ResultSet rs = stat.executeQuery();
			try {
				if (rs.next()) {
					String maxDate = rs.getString(1);
					if (maxDate != null && maxDate.length() == 10) {
						ret = queryColumnDefInfoBeans(conn, StringUtils.strToDate(maxDate),
								StringUtils.strToDate(maxDate));
					}
				}
			} finally {
				rs.close();
			}
		} finally {
			stat.close();
		}
		return ret;
	}
}
